package com.manager.base.impl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.hibernate.LockMode;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;

import com.manager.base.BaseDao;
import com.manager.base.BaseService;
import com.manager.util.Log;
import com.manager.util.Stringbuffer;

public class BaseServiceImpl implements BaseService {
	private BaseDao baseDao;
//	private DataSource dataSource;
	
	/**
	 * ----------------------------------JDBCTemplate方法----------------------------------
	 */
	public List queryList(String sql) {
		return baseDao.queryList(sql);
	}

	public List queryList(String sql, Object... params) {
		return baseDao.queryList(sql, params);
	}
	
	public int queryInt(String sql){
		return baseDao.queryInt(sql);
	}
	public int queryInt(String sql, Object... params){
		return baseDao.queryInt(sql, params);
	}
	
	public boolean queryBoolean(String sql){
		List list = baseDao.queryList(sql);
		if(list.size() > 0){
			return true;
		}else{
			return false;
		}
	};
	
	public boolean queryBoolean(String sql, Object... params){
		List list = baseDao.queryList(sql, params);
		if(list.size() > 0){
			return true;
		}else{
			return false;
		}
	};
	
	public String queryString(String sql){
		List<Map> list = baseDao.queryList(sql);
		if(list.size() == 0){
			return null;
		}else{
			Map map = list.get(0);
			Iterator itor = map.entrySet().iterator();
			Entry entry = (Entry)itor.next();
			String rs = null;
			
			return (String)map.get(entry.getKey());
		}
	};
	
	public String queryString(String sql, Object... params){
		List<Map> list = baseDao.queryList(sql, params);
		if(list.size() == 0){
			return null;
		}else{
			Map map = list.get(0);
			Iterator itor = map.entrySet().iterator();
			Entry entry = (Entry)itor.next();
			
			return (String)map.get(entry.getKey());
		}
	};
	
	public Map queryMap(String sql){
		List list = baseDao.queryList(sql);
		if(list.size() == 0){
			return null;
		}else{
			return (Map)list.get(0);
		}
	}
	
	public Map queryMap(String sql, Object... params){
		List list = this.baseDao.queryList(sql, params);
		if(list.size() == 0){
			return null;
		}else{
			return (Map)list.get(0);
		}
	}
	
	public void add(String sql){
		baseDao.add(sql);
	};
	
	public void add(String sql, Object... params){
		baseDao.add(sql, params);
	};
	
	public void add(String sql, ArrayList params){
		Object[] objs = new Object[params.size()];
		for(int i = 0; i < params.size(); i++){
			objs[i] = params.get(i);
		}
		baseDao.add(sql, objs);
	}
	
	/*
	 * 1：table-->tableName
	 * 2: coloumName-->coloumValue (至少一个要update,insert的键值对)
	 * eg; INSERT INTO TABLENAME (name,age,email) VALUES (?,?,?)
	 */
	public void add(String table, LinkedHashMap params){
		//组装带参数的sql
		Object[] objs = this.getAddSql(table, params);
		baseDao.add((String)objs[0], (Object[])objs[1]);
	}
	
	/*
	 * 当向主表插入一条数据后，返回这条记录的PK
	 */
	public int addForPK(String table, LinkedHashMap params){
		final Object[] objs = this.getAddSql(table, params);
		return baseDao.add(new PreparedStatementCreator(){
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement((String)objs[0], Statement.RETURN_GENERATED_KEYS); 
				for(int k = 0; k < ((Object[])objs[1]).length; k++){
					ps.setObject(k + 1, ((Object[])objs[1])[k]);
				}
				return ps; 
			}
		}, new GeneratedKeyHolder());
	}
	
	public void delete(String sql){
		baseDao.delete(sql);
	};
	
	public void delete(String sql, Object... params){
		baseDao.delete(sql, params);
	};
	
	public void delete(String sql, ArrayList params){
		Object[] objs = new Object[params.size()];
		for(int i = 0; i < params.size(); i++){
			objs[i] = params.get(i);
		}
		baseDao.delete(sql, objs);
	}
	
	/*
	 * tabel 表名
	 * whereParams where条件的键值对
	 * eg: DELETE FROM TABLENAME WHERE name = ? AND age = ?
	 */
	public void delete(String table, LinkedHashMap whereParams){
		int i = 0;
		Iterator itrWhere = null;
		Object[] objs = null;
		Stringbuffer sb = new Stringbuffer();
		sb.append("DELETE FROM ").append(table);
		
		if(whereParams.size() > 0){
			itrWhere = whereParams.entrySet().iterator();
			sb.append(" WHERE ");
			objs = new Object[whereParams.size()];
			
			//添加delete from table where name = ? and age = ? 等键值对
			while(itrWhere.hasNext()){
				Entry entry = (Entry)itrWhere.next();
				//添加列名
				sb.append(entry.getKey().toString()).append(" = ?");
				if(i != whereParams.size() - 1){
					sb.append(" AND ");
				} 
				//参数数组
				objs[i] = entry.getValue();
				i++;
			}
		}
		Log.info("delete SQL: " + sb.toString());
		
		baseDao.update(sb.toString(), objs);
	}
	
	public void update(String sql){
		baseDao.update(sql);
	}
	public void update(String sql, Object... params){
		baseDao.update(sql, params);
	}
	
	public void update(String sql, ArrayList params){
		Object[] objs = new Object[params.size()];
		for(int i = 0; i < params.size(); i++){
			objs[i] = params.get(i);
		}
		baseDao.update(sql, objs);
	}
	
	/*
	 * tabel 表名
	 * setParams 列的键值对
	 * whereParams where条件的键值对(可以为空的new LinkedHashMap() 或 null，表示没有where条件)
	 * eg: UPDATE TABLENAME SET name = ?, age = ? WHERE id = ? AND active = ?
	 */
	public void update(String table, LinkedHashMap setParams, LinkedHashMap whereParams){
		int i = 0;
		int j = 0;
		Iterator itrSet = null;
		Iterator itrWhere = null;
		Stringbuffer sb = new Stringbuffer();
		Object[] objs = null;
		
		sb.append("UPDATE ").append(table).append(" SET ");
		
		itrSet = setParams.entrySet().iterator();
		if(whereParams == null || whereParams.size() == 0){
			objs = new Object[setParams.size()];
		}else{
			objs = new Object[setParams.size() + whereParams.size()];
			itrWhere = whereParams.entrySet().iterator();
		}
		
		
		//添加update table set name = ?, age = ? 等键值对
		while(itrSet.hasNext()){
			Entry entry = (Entry)itrSet.next();
			sb.append(entry.getKey().toString()).append(" = ?");
			if(i != setParams.size() - 1){
				sb.append(", ");
			} 
			objs[i] = entry.getValue();
			i++;
		}
		
		//添加update table ... where active = ? and sex = ? 等键值对
		if(whereParams != null && whereParams.size() > 0){
			sb.append(" WHERE ");
			while(itrWhere.hasNext()){
				Entry entry = (Entry)itrWhere.next();
				//添加列名
				sb.append(entry.getKey().toString()).append(" = ?");
				if(j != whereParams.size() - 1){
					sb.append(" AND ");
				} 
				//参数数组
				objs[i] = entry.getValue();
				i++;
				j++;
			}
		}
		
		Log.info("update SQL: " + sb.toString());
		
		baseDao.update(sb.toString(), objs);
	}
	
	/*
	 * 通过sql(不带参数)批量插入，删除，更新数据
	 * 注：
	 * (1)SQL类型不限
	 * (2)由于其内部使用了Statement语句，所以数据的操作效率一般
	 * (3)适合于小批量数据的CUD（增、更新、删除）操作
	 */
	public int executeBatch(String[] sqls){
		long startTime = System.currentTimeMillis();
		
		int total =  baseDao.executeBatch(sqls);
		
		double estimatedTime = System.currentTimeMillis() - startTime;
		Log.info("batchAdd: " + total + " rows add success" + ",total " + estimatedTime/1000 + "秒");
		return total;
	}
	
	/*
	 * 通过sql批量插入，删除，更新数据
	 * 注:params[i][j]必须是Serializable类型,
	 * (1)始终是同一SQL(参数具有多样性)
	 * (2)由于其内部使用了PreparedStatement语句，所以数据的操作效率还是不错的
	 * (3)也仅仅适合于小批量数据的CUD（增、更新、删除）操作
	 * 用法：
	 * String sql = "INSERT INTO tablename (name, age) VALUES (?, ?)";
	 * Object[][] params = new Object[2][2];
	 * params[0][0] = "name1";
	 * params[0][1] = 20;
	 * params[1][0] = "name2";
	 * params[1][1] = 22;
	 * this.addBatch(sql, params);
	 */
	public int executeBatch(String sql, final Object[][] params){
		long startTime = System.currentTimeMillis();
		
		final int paramLength = params[0].length;
		int total =  baseDao.executeBatch(sql, new BatchPreparedStatementSetter(){//始终返回1
			/*
			 *index  如果这批数据有300条，则index从:0,1,2 ... 299
			 *i  sql语句中对数的位置，从"1"开始
			 *paramLength  sql语句中参数的个数
			 */
            public void setValues(PreparedStatement ps, int index) throws SQLException {
            	for(int i = 0; i < paramLength; i++){
					ps.setObject(i + 1, params[index][i]);
				}
           }

            public int getBatchSize() {
                return params.length;
            }
		});
		
		Log.info("batchExecute: " + params.length + " rows, " + getElapseTime(startTime) + "秒");
		return params.length;
	}
	
	/*
	 * 通过sql批量插入，删除，更新数据
	 * 注:同上
	 * 用法：
	 * String sql = INSERT INTO tableName (active, name, age) VALUES (?,?,?)
	 * ArrayList<ArrayList> params = new ArrayList<ArrayList>();
	 * ArrayList<Object> value  = new ArrayList<Object>();
	 * value.add(Byte.valueOf(1));
	 * value.add("yourname");
	 * value.add(Integer.valueOf(22));
	 * params.add(value);
	 * addBatch(sql, params);
	 */
	public int executeBatch(final String sql, final ArrayList<ArrayList> params){
		long startTime = System.currentTimeMillis();
		
		final int paramLength = params.get(0).size();
		int total =  baseDao.executeBatch(sql, new BatchPreparedStatementSetter(){//始终返回1
			/*
			 *index  如果这批数据有300条，则index从:0,1,2 ... 299
			 *k  sql语句中对数的位置，从"1"开始
			 *paramLength  sql语句中参数的个数
			 */
            public void setValues(PreparedStatement ps, int index) throws SQLException {
            	for(int k = 0; k < paramLength; k++){
					ps.setObject(k + 1, params.get(index).get(k));
				}
           }

            public int getBatchSize() {
                return params.size();
            }
		});
		
		Log.info("batchExecute: " + params.size() + " rows, "  + getElapseTime(startTime) + "秒");
		return params.size();
	}
	
	/*
	 * 与其它两个方法差不多，去掉此方法
	 * 通过sql批量插入，删除，更新数据
	 * 注:params[i][j]必须是Serializable类型,
	 * (1)始终是同一SQL(参数具有多样性)
	 * (2)可进行大批量数据的"分批操作"，比executeBatch()方法效率更高，batchaSize参数会影响效率
	 * 用法：
	 */
//	public int executeSqlBatch(String sql, Object[][] params, int[] sqlTypes, int batchSize) {
//		long startTime = System.currentTimeMillis();
//		
//		BatchSqlUpdate batchSqlUpdate = new BatchSqlUpdate(dataSource, sql);
//		batchSqlUpdate.setBatchSize(batchSize);
//		batchSqlUpdate.setTypes(sqlTypes);
//		
//		for (int i = 0; i < params.length; i++) {
//			batchSqlUpdate.update(params[i]);
//		}
//		
//		batchSqlUpdate.flush();
//		
//		Log.info("batchExecute: " + params.length + " rows, " + getElapseTime(startTime) + "秒");
//		return params.length;
//	}
	
	//only for execute DDL language, such as create table, alter table, create trigger ...
	public void execute(String sql){
		baseDao.execute(sql);
	}
	
	/*
	 * call a Stored procedure, return a List<LinkedHashMap>
	 * proName    存储过程名称
	 * params     变长参数
	 * 
	 * usage
	 * for example, your stored procedure is : PRO_EG01(?,?,?)
	 * proName is : PRO_EG01
	 * params is : 01, 02, 03
	 * note: if stored procedure has no params, just set params null
	 */
	public List callProList(String proName, Object ... params){
		return baseDao.callProList(proName, params);
	}
	
	/**
	 * ----------------------------------HibernateTemplate方法----------------------------------
	 */
	public <T> T getObject(Class<T> entityClass,  Serializable id){
		return baseDao.get(entityClass, id);
	}
	
	public <T> T getObject(Class<T> entityClass, Serializable id, LockMode lockMode){
		return baseDao.get(entityClass, id, lockMode);
	}
	
	public Object getObject(String hql, Object... params){
		List list = this.getList(hql, params);
		if(list.size() == 0){
			return null;
		}else{
			return list.get(0);
		}
	}
	
	public List getList(String hql){
		return baseDao.getList(hql);
	}
	
	public List getList(String hql, Object... params){
		return baseDao.getList(hql, params);
	}
	
	public List getListTop(String hql, int length){
		return baseDao.getListTop(hql, length);
	}
	public List getListPage(String hql, int offset, int length){
		return baseDao.getListPage(hql, offset, length);
	}
	
	public List getListBySql(String sql){
		return baseDao.getListBySql(sql);
	}
	
	public void save(Object entity){
		baseDao.save(entity);
	}
	
	public void saveOrUpdateAll(Collection entities){
		long startTime = System.nanoTime();
		
		baseDao.saveOrUpdateAll(entities);
		
		Log.info("saveOrUpdateAll: " + entities.size() + " rows, " + getElapseTime(startTime) + "秒");
	}
	
	public void delete(Object entity){
		baseDao.delete(entity);
	}
	
	public void delete(Class entityClass, Serializable id){
		baseDao.delete(entityClass, id);
	}
	
	public void deleteAll(Collection entities){
		long startTime = System.nanoTime();
		
		baseDao.deleteAll(entities);
		
		Log.info("deleteAll: " + entities.size() + " rows, " + getElapseTime(startTime) + "秒");
	}
	
	public void update(Object entity){
		baseDao.update(entity);
	}
	
	/**
	 * ----------------------------------其它辅助方法----------------------------------
	 */
	
	//计算时间差
	private double getElapseTime(long start){
		double elapseTime = System.currentTimeMillis() - start;
		return elapseTime/1000;
	}
	
	//组装带参数的sql
	private Object[] getAddSql(String table, LinkedHashMap params){
		int i = 0;
		Iterator itr = params.entrySet().iterator();
		Stringbuffer sb = new Stringbuffer();
		sb.append("INSERT INTO ").append(table).append(" (");
		
		Object[] objs = new Object[params.size()];
		while(itr.hasNext()){
			Entry entry = (Entry)itr.next();
			//添加insert into table (name = ?, age = ?) 等键值对
			sb.append(entry.getKey().toString());
			if(i != params.size() - 1){
				sb.append(",");
			} 
			//参数数组
			objs[i] = entry.getValue();
			i++;
		}
		
		//添加insert into values (?,?) 等键值对
		sb.append(") VALUES (");
		for(int j = 0; j < params.size(); j++){
			sb.append("?");
			if(j != params.size() - 1){
				sb.append(",");
			}
		}
		sb.append(")");
		Log.info("add SQL: " + sb.toString());
		
		return new Object[]{sb.toString(), objs};
	}
	
	/**
	 * getter and setter
	 */
	
	public void setBaseDao(BaseDao baseDao) {
		this.baseDao = baseDao;
	}
	
//	public void setDataSource(DataSource dataSource) {
//		this.dataSource = dataSource;
//	}
}
